package mhe.mgt.daos;


import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;


@SuppressWarnings("unchecked")
public class GenericDAOImpl implements GenericDAO {
	
	protected SessionFactory sessionFactory;
	private static GenericDAO instance;
	
	public GenericDAOImpl() {
		sessionFactory = new Configuration().configure(
				//new File("hibernate.cfg.xml")
				).buildSessionFactory();
		createTabel();
		
//		CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)"); 
//		cs.setString(1, backupdirectory);
		
//		jdbc:derby:sample;restoreFrom=c:\mybackups\sample
	}
	
	public static GenericDAO instance() {
		if (instance == null) {
			instance = new GenericDAOImpl();
		}
		return instance;
	}

	@SuppressWarnings({ "deprecation" })
	private void createTabel() {
		Session session = null;
		try{
			SessionFactory sessionFactory = getSessionFactory();
			session = sessionFactory.openSession();
			Transaction tx = session.beginTransaction();
			try {
				SQLQuery query = session.createSQLQuery("CREATE TABLE MCHSYSTEM " + //$NON-NLS-1$
						"(ID INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT SYSTEM_PK PRIMARY KEY, " + //$NON-NLS-1$
						"NAME VARCHAR(50), CREATE_DATE TIMESTAMP, UPDATE_DATE TIMESTAMP, NOTE VARCHAR(5000), " + //$NON-NLS-1$
						"CREATE_USER VARCHAR(50))"); //$NON-NLS-1$
				query.executeUpdate();
			} catch (Exception e) {
				System.out.println("Table [SYSTEM] exist."); //$NON-NLS-1$
			}
			try {
				SQLQuery query = session.createSQLQuery("CREATE TABLE MCHMACHINE " + //$NON-NLS-1$
						"(ID INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT MACHINE_PK PRIMARY KEY, " + //$NON-NLS-1$
						"SYSTEM_ID NUMERIC(19,0), NAME VARCHAR(50), CREATE_DATE TIMESTAMP, UPDATE_DATE TIMESTAMP, " + //$NON-NLS-1$
						"NOTE VARCHAR(5000), CREATE_USER VARCHAR(50))"); //$NON-NLS-1$
				query.executeUpdate();
			} catch (Exception e) {
				System.out.println("Table [MACHINE] exist."); //$NON-NLS-1$
			}
			try {
				SQLQuery query = session.createSQLQuery("CREATE TABLE MCHMACHINEPART " + //$NON-NLS-1$
						"(ID INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT MACHINE_PART_PK PRIMARY KEY, " + //$NON-NLS-1$
						"MACHINE_ID NUMERIC(19,0), MAX_LIFE_DAY  NUMERIC(10,0), NAME VARCHAR(50), CREATE_DATE TIMESTAMP, " + //$NON-NLS-1$
						"UPDATE_DATE TIMESTAMP, NOTE VARCHAR(5000), CREATE_USER VARCHAR(50))"); //$NON-NLS-1$
				query.executeUpdate();
			} catch (Exception e) {
				System.out.println("Table [MACHINEPART] exist."); //$NON-NLS-1$
			}
			session.flush();
			tx.commit();
			Connection con = session.connection();
			DatabaseMetaData dbmeta = con.getMetaData();
			ResultSet tables = dbmeta.getTables(con.getCatalog(), null, "MCH%", null); //$NON-NLS-1$
			while (tables.next()) {
				System.out.println("table = " + tables.getString("TABLE_NAME")); //$NON-NLS-1$ //$NON-NLS-1$
				ResultSet columns = dbmeta.getColumns(null, null, tables.getString("TABLE_NAME"), null); //$NON-NLS-1$
				int i = 1;
				while (columns.next())
				{
					System.out.printf("%d: %s (%d) %s\n", i++, columns.getString("COLUMN_NAME"),  //$NON-NLS-1$ //$NON-NLS-1$
					columns.getInt("ORDINAL_POSITION"), columns.getString("TYPE_NAME")); //$NON-NLS-1$ //$NON-NLS-1$
				}
				System.out.println("========================================"); //$NON-NLS-1$
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());;
		} finally {
			close(session);
		}
	}

	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	public void add(Object entity) {
		Session session = null;
		try{
			SessionFactory sessionFactory = getSessionFactory();
			session = sessionFactory.openSession();
			Transaction tx = session.beginTransaction();
			session.persist(entity);
			session.flush();
			tx.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(session);
		}
	}

	public Object merge(Object entity) {
		Session session = null ;
		try {
			SessionFactory sessionFactory = getSessionFactory();
			session = sessionFactory.openSession();
			Object result = session.merge(entity);
			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			close(session);
		}
	}

	public void update(Object entity) {
		Session session = null ;
		try {
			SessionFactory sessionFactory = getSessionFactory();
			session = sessionFactory.openSession();
			Transaction tx = session.beginTransaction();		
			session.update(entity);
			tx.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(session);
		}
	}

	public void delete(Object entity) {
		Session session = null ;
		try {
			SessionFactory sessionFactory = getSessionFactory();
			session = sessionFactory.openSession();
			session.delete(entity);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(session);
		}
	}

	public List<?> list(Class objectClass) {
		Session session = null ;
		try {
			SessionFactory sessionFactory = getSessionFactory();
			session = sessionFactory.openSession();
			String sql = "from " + objectClass.getSimpleName(); //$NON-NLS-1$
			Query query = session.createQuery(sql);
			List result = query.list();
			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			close(session);
		}
	}

	public Object find(Class objectClass, long id) {
		
		SessionFactory sessionFactory = getSessionFactory();
		Session session = sessionFactory.openSession();
		Object result=null;
		try {
			result = session.get(objectClass, id);
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally{
			close(session);
		}
		return result;
	}

	public List<?> list(Class<?> objectClass, String[] fieldNames, Object[] fieldValues, boolean andCompare,
			Map<String, Boolean> sortFields, int start, int length) {
		return list(objectClass, fieldNames, fieldValues, andCompare, sortFields, start, length, null);
	}

	public List<?> executeQuery(String sql, Object[] values, int start, int length) {
		Session session = null ;
		try {
			SessionFactory sessionFactory = getSessionFactory();
			session = sessionFactory.openSession();
			Query query = session.createQuery(sql);
			if (values != null) {
				int index = 1;
				for (Object value : values) {
					query.setParameter("value" + index, value); //$NON-NLS-1$
					index++;
				}
			}
			if (start >= 0) {
				query.setFirstResult(start);
			}
			if (length >= 0) {
				query.setMaxResults(length);
			}
			List result = query.list();
			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			close(session);
		}
	}
	
	public List<?> list(Class<?> objectClass, String[] fieldNames, Object[] fieldValues, boolean andCompare,
			Map<String, Boolean> sortFields, int start, int length, String loadField) {
		Session session = null ;
		try {
			SessionFactory sessionFactory = getSessionFactory();
			session = sessionFactory.openSession();
			String sql = "from " + objectClass.getSimpleName() + " entt"; //$NON-NLS-1$ //$NON-NLS-1$
			if (loadField != null) {
				sql = "select " + loadField + " " + sql; //$NON-NLS-1$ //$NON-NLS-1$
			}
			int index = 0;
			if (fieldNames != null) {
				List<String> list = Arrays.asList(fieldNames);
				fieldLoop: for (String fieldName : fieldNames) {
					if (fieldName.endsWith("SearchTag")) { //$NON-NLS-1$
						index++;
						continue fieldLoop;
					}
					if (sql.contains(" where ")) { //$NON-NLS-1$
						sql += andCompare ? " AND " : " OR "; //$NON-NLS-1$ //$NON-NLS-1$
					} else {
						sql += " where "; //$NON-NLS-1$
					}
					if (fieldValues[index] instanceof String) {
						if (list.contains(fieldName + "SearchTag")) { //$NON-NLS-1$
							sql += "(lower(entt." + fieldName + ") like lower(:value" + index + ") or lower(entt." //$NON-NLS-1$ //$NON-NLS-1$ //$NON-NLS-1$
									+ fieldName + "SearchTag) like lower(:value" + index + "))"; //$NON-NLS-1$ //$NON-NLS-1$
						} else {
							sql += "lower(entt." + fieldName + ") like lower(:value" + index + ")"; //$NON-NLS-1$ //$NON-NLS-1$ //$NON-NLS-1$
						}
					} else if (fieldValues[index] instanceof Object[]) {
						Object[] temp = (Object[]) fieldValues[index];
						if (temp[0] != null || temp[1] != null) {
							if (temp[0] != null && temp[1] != null) {
								sql += "entt." + fieldName + ">=:value" + index + "min AND entt." + fieldName + "<=:value" //$NON-NLS-1$ //$NON-NLS-1$ //$NON-NLS-1$ //$NON-NLS-1$
										+ index + "max"; //$NON-NLS-1$
							} else if (temp[0] == null) {
								sql += "entt." + fieldName + "<=:value" + index + "max"; //$NON-NLS-1$ //$NON-NLS-1$ //$NON-NLS-1$
							} else {
								sql += "entt." + fieldName + ">=:value" + index + "min"; //$NON-NLS-1$ //$NON-NLS-1$ //$NON-NLS-1$
							}
						}
					} else {
						sql += "entt." + fieldName + "=:value" + index; //$NON-NLS-1$ //$NON-NLS-1$
					}
					index++;
				}
			}
			if (sortFields != null) {
				index = 0;
				for (Entry<String, Boolean> sort : sortFields.entrySet()) {
					if (index > 0) {
						sql += ", "; //$NON-NLS-1$
					} else {
						sql += " order by "; //$NON-NLS-1$
					}
					sql += sort.getKey() + (sort.getValue() ? " ASC" : " DESC"); //$NON-NLS-1$ //$NON-NLS-1$
					index++;
				}
			}
			Query query = session.createQuery(sql);
			if (fieldNames != null) {
				for (int i = 0; i < fieldNames.length; i++) {
					Object value = fieldValues[i];
					if (value instanceof Object[]) {
						Object[] temp = (Object[]) value;
						if (temp[0] != null || temp[1] != null) {
							if (temp[0] != null) {
								query.setParameter("value" + i + "min", temp[0]); //$NON-NLS-1$ //$NON-NLS-1$
							}
							if (temp[1] != null) {
								query.setParameter("value" + i + "max", temp[1]); //$NON-NLS-1$ //$NON-NLS-1$
							}
						}
					} else {
						String fieldName = fieldNames[i];
						if (!fieldName.endsWith("SearchTag")) { //$NON-NLS-1$
							query.setParameter("value" + i, value); //$NON-NLS-1$
						}
					}
				}
			}
			if (start >= 0) {
				query.setFirstResult(start);
			}
			if (length >= 0) {
				query.setMaxResults(length);
			}
			List result = query.list();
			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			close(session);
		}
	}

	public void close(Session session) {
		
		if(session!=null){
			session.flush();
			session.close();
		}
		
	}

	public void executeSQLQuery(String sql, Object[] values) {
		Session session = null ;
		try {
			SessionFactory sessionFactory = getSessionFactory();
			session = sessionFactory.openSession();
			SQLQuery query = session.createSQLQuery(sql);
			if (values != null) {
				for (int i = 0; i < values.length; i++) {
					query.setParameter("param" + i, values[i]); //$NON-NLS-1$
				}
			}
			query.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(session);
		}
	}
	
	public void shutDown() {
		if (sessionFactory != null && !sessionFactory.isClosed()) {
			sessionFactory.close();
		}
	}
}
